<?php
defined( "AZ_EXE_" ) or die ( "CORE VARIABLE IS NOT DEFINED." );
	class UserExpireModel extends Model {
		private $queryGetRow = "";
        public function getExpires(){
            $result = $this->executeQueryAsObject( "SELECT * FROM expiredates;" ) ;
            return $result;
        }
		public function display( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("first_name","last_name","label") , $sortColumn="ue.id,ue.created,ue.modified" , $sortCriteria="ASC" , $published = false ){
			
			$start = is_numeric($start)&&$start>-1?$start:0;
			$perpage = is_numeric($perpage)&&$perpage>-1?$perpage:20;
			
			$result = array();
			$query = "SELECT u.id as user_id , u.first_name , u.last_name , e.id as e_id , e.option , e.label , ue.id as ue_id , ue.start_date , ue.end_date, ue.status
                    FROM userexpireds AS ue , expiredates e , users as u
                    WHERE ue.user_id = u.id
                    AND   ue.expire_id = e.id
                    AND   u.type = 2 " ;
			if ( $searchString != "" ){
				$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("first_name","last_name","label") : $searchColumns ; 
				if ( is_array($searchColumns) && !empty($searchColumns) ){
				$conditions = "" ;
					$conditions = array() ;
					foreach( $searchColumns AS $columnName ){
						$conditions[] = " $columnName LIKE '%$searchString%' " ;
					}
					$conditions = " AND ( " . implode("OR", $conditions ) . " ) " . ( $published!==false? " AND published = '$published' " : "" );
					$sortColumn = $sortColumn===""||$sortColumn===false?"ue.id,ue.created,ue.modified":$sortColumn;
					$sortCriteria = $sortCriteria===""||$sortCriteria===false?"DESC":$sortCriteria;
					$query .= "$conditions " ;
				}
			}
			if( $sortColumn != "" || $sortColumn != false ){
				$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"DESC";
				$query .= " ORDER BY $sortColumn $sortCriteria " ;
			}
			$this->queryGetRow = $query ;
			$query .= " LIMIT ".($start*$perpage)." , ".$perpage."; " ;
			//echo $query;
            //exit();
			$result = $this->executeQueryAsObject( $query ) ;
			return $result;
		}
		public function getTotalRecords(){
			return $this->getRowWithQuery($this->queryGetRow) ;
		}
		public function getMenu(){
			return $this->executeQueryAsObject("SELECT id,menutype,name_kh,name_en FROM az_menu WHERE `published` = 1 ORDER BY menutype,name_en,name_kh ASC");
		}
		public function add( $data = array() ){
			date_default_timezone_set("Asia/Bangkok");
			$fields = "";
			foreach($data as $table_name => $all_fields){
				$query = "INSERT INTO " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `created` = '" . date('Y-m-d') . "', `modified` = '" . date('Y-m-d') . "'";//die($query);
				return $this->executeQuery($query);
			}
		}
		public function edit( $data = array() ){
			date_default_timezone_set("Asia/Bangkok");
			$fields = "";
			foreach($data as $table_name => $all_fields){
				$query = "UPDATE " . $table_name . " SET ";
				foreach($all_fields as $field_name => $value){
					$fields .= "`" . $field_name . "` = '" . $this->sqlRealEscape($value) . "',";
					//print_r($table_name);
				}
				$query .= $fields . " `modified` = '".date('Y-m-d')."' WHERE `id` = " . $data[$table_name]['id'];
				return $this->executeQuery($query);
			}
		}
		public function delete( $id = 0 ){
			$id = is_array($id)&&!empty($id)?implode(",",$id):$id;
			$this->executeQuery( "DELETE FROM userexpireds WHERE id IN (" . $id . ")");
		}
        public function deleteWithUser( $id ){
            $this->executeQuery( "DELETE FROM userexpireds WHERE user_id = $id ; ");
        }
		public function find( $id ){
			return $this->executeQueryAsObject("SELECT * FROM userexpireds WHERE id = '$id' ;");
		}
        public function getExpireWithUserId( $id ){
            return $this->executeQueryAsObject("SELECT * FROM userexpireds WHERE user_id = '$id' ;");
        }
		public function getCategory($menu_id = 0){
			$query = "";
			if($menu_id != 0){
				$query = "SELECT `id`,`title_en` as title,`menu_id` FROM az_categories WHERE `published` = 1 AND `menu_id` = $menu_id ORDER BY `id`,`title_en` ASC";
			}else{
				$query = "SELECT `id`,`title_en` as title,`menu_id` FROM az_categories WHERE `published` = 1 ORDER BY `id`,`title_en` ASC";
			}
			return $this->executeQueryAsObject($query);
		}
		public function getLastArticle($number=5){
			$query = "SELECT * FROM az_articles WHERE published = 1 ORDER BY id DESC LIMIT 0,$number" ;
			return $this->executeQueryAsObject($query);
		}
		public function search($start=1 , $perpage=20 , $searchString="" ){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT
				m.id as menu_id,m.name_en as menu_name,m.type as menu_type,m.description_en as menu_description,m.show_description as menu_show_description,
				c.id as cate_id,c.title_en as cate_title,c.description_en as cate_description,c.show_description as cate_show_description,
				a.id as article_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext,a.created as article_created, a.type as article_type , 
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}else{
				$query = "
				SELECT
				m.id as menu_id,m.name_kh as menu_name,m.type as menu_type,m.description_kh as menu_description,m.show_description as menu_show_description,
				c.id as cate_id,c.title_kh as cate_title,c.description_kh as cate_description,c.show_description as cate_show_description,
				a.id as article_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext,a.created as article_created, a.type as article_type , 
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}
			$query .= "
			FROM
			az_menu as m , az_categories as c , az_articles as a
			WHERE
			m.id = c.menu_id AND c.id = a.catid AND
			a.published = 1 ";
			if ( $searchString != "" ){
				if ( $lang=="en"){
					$query .= " AND ( a.title_en LIKE '%$searchString%' OR a.fulltext_en LIKE '%$searchString%' ) ";
				}else{
					$query .= " AND ( a.title_kh LIKE '%$searchString%' OR a.fulltext_kh LIKE '%$searchString%' ) ";
				}
			}
			$query .= " 
				ORDER BY a.`id` DESC, a.`created` DESC
				LIMIT ".(($start-1)*$perpage).",$perpage" ;
			return $this->executeQueryAsObject($query);
			
		}
		/* article at front page */
		public function getTopArticleByMenuID($menu_id = 0,$limit = "4"){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
					SELECT 
						m.id as menu_id,m.name_en as menu_name,m.type as menu_type,m.description_en as menu_description,m.show_description as menu_show_description,
						c.id as cate_id,c.title_en as cate_title,c.description_en as cate_description,c.show_description as cate_show_description,
						a.id as article_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext ,  a.type as article_type , 
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}else{
				$query = "
					SELECT
						m.id as menu_id,m.name_kh as menu_name,m.type as menu_type,m.description_kh as menu_description,m.show_description as menu_show_description,
						c.id as cate_id,c.title_kh as cate_title,c.description_kh as cate_description,c.show_description as cate_show_description,
						a.id as article_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext, a.type as article_type , 
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}
			$query .= "
					FROM
						az_menu as m , az_categories as c , az_articles as a
					WHERE
						m.id = c.menu_id AND c.id = a.catid AND
						a.published = 1 AND m.id = '$menu_id'
					ORDER BY a.`hits` DESC
					LIMIT 0,$limit";
			return $this->executeQueryAsObject($query);
		}
		public function getLatestArticle($lang = "en",$limit = "5", $page=1){
            $lang = $_SESSION['lang'];
            $page = $page<1?1:$page;
            $queryTotalRecord = "" ;
            if($lang == "en"){
                $query = $queryTotalRecord =  "
                    SELECT 
                        m.id as menu_id,m.name_en as menu_name,m.type as menu_type,m.description_en as menu_description,m.show_description as menu_show_description,
                        c.id as cate_id,c.title_en as cate_title,c.description_en as cate_description,c.show_description as cate_show_description,
                        a.id as article_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext ,a.created, a.type as article_type , 
                        (SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
                ";
            }else{
                $query = $queryTotalRecord = "
                    SELECT
                        m.id as menu_id,m.name_kh as menu_name,m.type as menu_type,m.description_kh as menu_description,m.show_description as menu_show_description,
                        c.id as cate_id,c.title_kh as cate_title,c.description_kh as cate_description,c.show_description as cate_show_description,
                        a.id as article_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext,a.created, a.type as article_type , 
                        (SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
                        ";
            }
            $queryTotalRecord .= "
                    FROM
                        az_menu as m , az_categories as c , az_articles as a
                    WHERE
                        m.id = c.menu_id AND c.id = a.catid AND
                        m.published
                    ORDER BY a.`created`DESC
                    ";
            $total = $this->executeQuery($queryTotalRecord);
            $total = ceil(mysql_num_rows($total)/$limit);
            $query .= "
                    FROM
                        az_menu as m , az_categories as c , az_articles as a
                    WHERE
                        m.id = c.menu_id AND c.id = a.catid AND
                        m.published
                    ORDER BY a.`created`DESC
                    LIMIT " .(($page-1)*$limit). "," . $limit;
                $result = array("page"=>$page,"total"=>$total,"articles"=>$this->executeQueryAsObject($query));
            return $result;
        }
		public function getLatestArticleByMenuID($menu_id = 0,$limit = "5"){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT
				m.id as menu_id,m.name_en as menu_name,m.type as menu_type,m.description_en as menu_description,m.show_description as menu_show_description,
				c.id as cate_id,c.title_en as cate_title,c.description_en as cate_description,c.show_description as cate_show_description,
				a.id as article_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext ,a.created, a.type as article_type , 
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}else{
				$query = "
				SELECT
				m.id as menu_id,m.name_kh as menu_name,m.type as menu_type,m.description_kh as menu_description,m.show_description as menu_show_description,
				c.id as cate_id,c.title_kh as cate_title,c.description_kh as cate_description,c.show_description as cate_show_description,
				a.id as article_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext,a.created, a.type as article_type , 
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}
			$query .= "
			FROM
			az_menu as m , az_categories as c , az_articles as a
			WHERE
			m.id = c.menu_id AND c.id = a.catid AND
			a.published = 1 AND m.id='$menu_id'
			ORDER BY a.`created`DESC
			LIMIT 0," . $limit;
			return $this->executeQueryAsObject($query);
		}
		public function getLatestArticleByHit($lang = "en",$limit = "5"){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
					SELECT 
						m.id as menu_id,m.name_en as menu_name,m.type as menu_type,m.description_en as menu_description,m.show_description as menu_show_description,
						c.id as cate_id,c.title_en as cate_title,c.description_en as cate_description,c.show_description as cate_show_description,
						a.id as article_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext, a.type as article_type , 
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author 
				";
			}else{
				$query = "
					SELECT
						m.id as menu_id,m.name_kh as menu_name,m.type as menu_type,m.description_kh as menu_description,m.show_description as menu_show_description,
						c.id as cate_id,c.title_kh as cate_title,c.description_kh as cate_description,c.show_description as cate_show_description,
						a.id as article_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext, a.type as article_type , 
						(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}
			$query .= "
					FROM
						az_menu as m , az_categories as c , az_articles as a
					WHERE
						m.id = c.menu_id AND c.id = a.catid AND
						a.published = 1
					ORDER BY a.`hits` DESC, a.`created` DESC
					LIMIT 0," . $limit;
					//die($query);
			return $this->executeQueryAsObject($query);
		}
		public function getLatestArticleByID($article_id = "0", $lang = "en",$limit = "1"){
			$lang = $_SESSION['lang'];
			if($lang == "en"){
				$query = "
				SELECT
				m.id as menu_id,m.name_en as menu_name,m.type as menu_type,m.description_en as menu_description,m.show_description as menu_show_description,
				c.id as cate_id,c.title_en as cate_title,c.description_en as cate_description,c.show_description as cate_show_description,
				a.id as article_id,a.title_en as article_title,a.introtext_en as article_introtext,a.fulltext_en as article_fulltext,a.created, a.type as article_type , 
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}else{
				$query = "
				SELECT
				m.id as menu_id,m.name_kh as menu_name,m.type as menu_type,m.description_kh as menu_description,m.show_description as menu_show_description,
				c.id as cate_id,c.title_kh as cate_title,c.description_kh as cate_description,c.show_description as cate_show_description,
				a.id as article_id,a.title_kh as article_title,a.introtext_kh as article_introtext,a.fulltext_kh as article_fulltext,a.created, a.type as article_type , 
				(SELECT CONCAT(CONCAT(u.`first_name`, ' ') , u.`last_name`) FROM `users` as u WHERE a.created_by = u.id) as author
				";
			}
			$query .= "
			FROM
			az_menu as m , az_categories as c , az_articles as a
			WHERE
			m.id = c.menu_id AND c.id = a.catid AND
			m.published AND a.id = $article_id
			LIMIT 0," . $limit;
			//die($query);
			$results = $this->executeQueryAsObject($query);
			if(count($results)){
				$sql = "UPDATE `az_articles` SET `hits` = (`hits` + 1) WHERE `id` ='" . $article_id . "'";
				$this->executeQuery($sql);
			}
			return $results;
		}
		public function getAllArticles(){
			$query = "SELECT * FROM az_articles" ;
			return $this->executeQueryAsObject($query);
		}
		public function getComment($article_id = 0){
			$query = "
				SELECT fb.`comment`,fb.`comment_response`, fb.`reply`, fb.`status`,fb.`created`,fb.`modified`,fb.`user_id`,
				CONCAT(CONCAT(u.`first_name`,' '),u.`last_name`) as username,
				(SELECT CONCAT(CONCAT(u2.`first_name`,' '),u2.`last_name`) FROM `users` as u2 WHERE fb.`replied_by` = u2.id) as reply_by
				FROM `az_article_feedbacks` as fb INNER JOIN `users` as u ON
				fb.user_id = u.id
				WHERE `status` <> -1 AND fb.`article_id` = " . $article_id . "
				ORDER BY fb.`created` DESC
			";
			return $this->executeQueryAsObject($query);
		}
	}
?>